package com.szholly.pro.watergas_server.ps.android.controller;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.util.CollectionUtils;

import com.szholly.pro.watergas_server.common.data.DataUtil;
import com.szholly.pro.watergas_server.ps.android.common.NonNullUtil;
import com.szholly.pro.watergas_server.ps.android.common.ValidUtils;
import com.szholly.utils.util.StringUtils;
import com.szholly.data.general.GlobalParameter;
import com.szholly.data.general.RowBase;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;

/**
 * 排水管网服务
 * 
 * 2017/5/17 <br/>
 *
 * @author Joey Huang QQ:273179370）
 */
public class PipeService {
	private final String TYPE_JSON = "{\"排水管网\":[\"雨水\",\"污水\",\"合流\"],\"排水管网设施\":[\"检查井\",\"沉砂池\",\"阀门\",\"排水泵站\",\"雨水口\",\"排放口\",\"管线点\",\"溢流堰\"],\"污水处理厂\":[\"国控\",\"省控\",\"市控\",\"其他\"],\"管网监测点\":[\"检查井\",\"沉砂池\",\"阀门\",\"排水泵站\",\"雨水口\",\"排放口\",\"管线点\",\"溢流堰\"],\"清源小区\":[\"达标\",\"未达标\",\"新建\",\"其它\"],\"规划管网\":[],\"内窥检测管网\":[\"无缺陷\",\"低\",\"中\",\"高\",\"严重\"]}";

	public final static PipeService INSTANCE = new PipeService();
	private DataModel mDataModel;

	public static PipeService getInstance() {
		return INSTANCE;
	}

	// 获取属性复核数据
	public RowBase getEndoscopicReviewDetail(String pipeCode, int pageIndex) throws Exception {
		ValidUtils.hasText(pipeCode, "管道编号为空，属性复核数据获取失败");
		ValidUtils.isTrue(pageIndex >= 1, "页码编号小于1，属性复核数据获取失败");

		List<GlobalParameter> parms = new ArrayList<GlobalParameter>();
		parms.add(new GlobalParameter("pageIndex", pageIndex));
		parms.add(new GlobalParameter("pipeCode", pipeCode));
		String sql = "SELECT ATTR_REVIEW_ID,\n"
				+ "       NO,\n"
				+ "       FLOW,\n"
				+ "       PIPE_TYPE,\n"
				+ "       PIPE_MATERIAL,\n"
				+ "       PIPE_DIAMETER,\n"
				+ "       SURVEYOR,\n"
				+ "       WELL_NO_REVIEW,\n"
				+ "       WELL_NO_DESIGN,\n"
				+ "       t2.S_DOMAINCAPTION PIPE_MATERIAL_REVIEW,\n"
				+ "       t1.S_DOMAINCAPTION PIPE_MATERIAL_DESIGN,\n"
				+ "       PIPE_DIAMETER_VIEW,\n"
				+ "       PIPE_DIAMETER_DESIGN,\n"
				+ "       SECTION_SIZE_REVIEW,\n"
				+ "       SECTION_SIZE_DESIGN,\n"
				+ "       X_REVIEW,\n"
				+ "       Y_REVIEW,\n"
				+ "       X_DESIGN,\n"
				+ "       Y_DESIGN,\n"
				+ "       PILE_TOP_REVIEW,\n"
				+ "       PILE_TOP_DESIGN,\n"
				+ "       PLANE_DIFF,\n"
				+ "       HEIGHT_DIFF,\n"
				+ "       ATTR_REVIEW_ADDRESS,\n"
				+ "       TO_CHAR(DETECTION_DATE, 'yyyy:mm:dd hh24:mi:ss') as DETECTION_DATE,\n"
				+ "       PIPE_NO\n"
				+ "  from attr_review t\n"
				+ "  LEFT JOIN (select S_DOMAINCODE, S_DOMAINCAPTION\n"
				+ "               from DATA_DEFULATDOMAIN\n"
				+ "              where S_DOMAINNAME = 'PIPE_MATERIAL') t1\n"
				+ "    on t1.S_DOMAINCODE = t.PIPE_MATERIAL_DESIGN\n"
				+ "  LEFT JOIN (select S_DOMAINCODE, S_DOMAINCAPTION\n"
				+ "               from DATA_DEFULATDOMAIN\n"
				+ "              where S_DOMAINNAME = 'PIPE_MATERIAL') t2\n"
				+ "    on t2.S_DOMAINCODE = PIPE_MATERIAL_REVIEW\n"
				+ " where 1 = 1 and PIPE_NO = @pipeCode";
		List<RowBase> rows = DataUtil.getSelectRows(sql, parms);
		ValidUtils.isTrue(rows != null && rows.size() >= pageIndex, "页码编号有误没有找到该页数据");
		int count = rows.size();
		RowBase rowBase = rows.get(pageIndex - 1);
		List<RowBase> valueList = new ArrayList<>();
		RowBase bean;

		bean = new RowBase();
		valueList.add(bean);
		bean.put("designValue", NonNullUtil.toString(rowBase.getValue("WELL_NO_DESIGN")));
		bean.put("reviewValue", NonNullUtil.toString(rowBase.getValue("WELL_NO_REVIEW")));
		bean.put("valueName", "点号");

		bean = new RowBase();
		valueList.add(bean);
		bean.put("designValue", NonNullUtil.toString(rowBase.getValue("PIPE_MATERIAL_DESIGN")));
		bean.put("reviewValue", NonNullUtil.toString(rowBase.getValue("PIPE_MATERIAL_REVIEW")));
		bean.put("valueName", "材质");

		bean = new RowBase();
		valueList.add(bean);
		bean.put("designValue", NonNullUtil.toString(rowBase.getValue("PIPE_MATERIAL_DESIGN")));
		bean.put("reviewValue", NonNullUtil.toString(rowBase.getValue("PIPE_MATERIAL_REVIEW")));
		bean.put("valueName", "材质");

		bean = new RowBase();
		valueList.add(bean);
		bean.put("designValue", NonNullUtil.toString(rowBase.getValue("PIPE_DIAMETER_DESIGN")));
		bean.put("reviewValue", NonNullUtil.toString(rowBase.getValue("PIPE_DIAMETER_VIEW")));
		bean.put("valueName", "管径");

		bean = new RowBase();
		valueList.add(bean);
		bean.put("designValue", NonNullUtil.toString(rowBase.getValue("SECTION_SIZE_DESIGN")));
		bean.put("reviewValue", NonNullUtil.toString(rowBase.getValue("SECTION_SIZE_REVIEW")));
		bean.put("valueName", "断面尺寸");

		bean = new RowBase();
		valueList.add(bean);
		bean.put("designValue", NonNullUtil.toString(rowBase.getValue("X_DESIGN")));
		bean.put("reviewValue", NonNullUtil.toString(rowBase.getValue("X_REVIEW")));
		bean.put("valueName", "X");

		bean = new RowBase();
		valueList.add(bean);
		bean.put("designValue", NonNullUtil.toString(rowBase.getValue("Y_DESIGN")));
		bean.put("reviewValue", NonNullUtil.toString(rowBase.getValue("Y_REVIEW")));
		bean.put("valueName", "Y");

		bean = new RowBase();
		valueList.add(bean);
		bean.put("designValue", NonNullUtil.toString(rowBase.getValue("PILE_TOP_DESIGN")));
		bean.put("reviewValue", NonNullUtil.toString(rowBase.getValue("PILE_TOP_REVIEW")));
		bean.put("valueName", "管底标高");

		RowBase result = new RowBase();
		result.put("valueList", valueList);
		result.put("id", NonNullUtil.toString(rowBase.getValue("NO")));
		result.put("pipeNo", NonNullUtil.toString(rowBase.getValue("PIPE_NO")));
		result.put("flow", NonNullUtil.toString(rowBase.getValue("FLOW")));
		result.put("pipeType", NonNullUtil.toString(rowBase.getValue("PIPE_TYPE")));
		result.put("surveyor", NonNullUtil.toString(rowBase.getValue("SURVEYOR")));
		result.put("dateTime", NonNullUtil.toString(rowBase.getValue("DETECTION_DATE")));
		result.put("planeDiff", NonNullUtil.toString(rowBase.getValue("PLANE_DIFF")));
		result.put("heightDiff", NonNullUtil.toString(rowBase.getValue("HEIGHT_DIFF")));
		result.put("address", NonNullUtil.toString(rowBase.getValue("ATTR_REVIEW_ADDRESS")));
		result.put("defaultString", "(未查到相关信息)");

		AndroidPageInfo pageInfo = AndroidPageInfo.create(count, 1, pageIndex);
		result.put("pageInfo", pageInfo);
		return result;
	}

	// 获取属性复核数据
	public List<RowBase> getEndoscopicPrecisionList(String projectCode) throws Exception {
		if (mDataModel == null) {
			mDataModel = new DataModel();
		}
		List<GlobalParameter> parms = new ArrayList<GlobalParameter>();
		String projectSql = "select PROJECT_NO,PROJECT_NAME from project where 1 = 1";
		if (!StringUtils.IsNullOrSpace(projectCode)) {
			parms.add(new GlobalParameter("projectCode", projectCode));
			projectSql += " and PROJECT_NO = @projectCode";
		}
		projectSql += " order by PROJECT_NO desc";
		List<RowBase> rows = DataUtil.getSelectRows(projectSql, parms);
		if (CollectionUtils.isEmpty(rows)) {
			return new ArrayList<>();
		}
		List<RowBase> resultList = new ArrayList<>();
		for (RowBase rowBase : rows) {
			String projectNo = rowBase.getSafeString("PROJECT_NO");
			String projectName = rowBase.getSafeString("PROJECT_NAME");
			RowBase innerRowBase = getHeightPercent(projectNo);
			if (innerRowBase != null) {
				innerRowBase.put("projectName", projectName);
				resultList.add(innerRowBase);
			}
			innerRowBase = getPanelPercent(projectNo);
			if (innerRowBase != null) {
				innerRowBase.put("projectName", projectName);
				resultList.add(innerRowBase);
			}
		}
		return resultList;
	}

	/**
	 * 获取分区列表 {id,name,type,MINX,MINY,MAXX,MAXY}
	 */
	public List<RowBase> getUnitList() throws Exception {
		String sql = "SELECT substr(t1.xiancode, 7) \"id\",\n"
				+ "       t1.XIANNAME   \"name\",\n"
				+ "       '1001' as \"type\",\n"
				+ "       t1.shape.minx minx,\n"
				+ "       t1.shape.miny miny,\n"
				+ "       t1.shape.maxx maxx,\n"
				+ "       t1.shape.maxy maxy\n"
				+ "  FROM sde.qx t1 order by t1.xiancode";
		return NonNullUtil.toList(DataUtil.getSelectRows(sql, null));
	}

	/**
	 * 获取街道列表 {id,name,type,fatherId,MINX,MINY,MAXX,MAXY}
	 */
	public List<RowBase> getStreetList() throws Exception {
		String sql = "select t.jdcode \"id\",\n"
				+ "       t.jdname \"name\",\n"
				+ "       t.xiancode \"fatherId\",\n"
				+ "       '1002' as\"type\",\n"
				+ "       t.shape.minx minx,\n"
				+ "       t.shape.miny miny,\n"
				+ "       t.shape.maxx maxx,\n"
				+ "       t.shape.maxy maxy\n"
				+ "  from sde.jd t\n"
				+ " order by t.xiancode";
		return NonNullUtil.toList(DataUtil.getSelectRows(sql, null));
	}

	/**
	 * 获取排水管网类型列表 {id,name,type}
	 */
	public List<RowBase> getTypeList() throws Exception {
		List<RowBase> resultList = new ArrayList<>();
		JSONObject json = JSONObject.fromObject(TYPE_JSON);
		for (Object typeName : json.keySet()) {
			RowBase rowBase = new RowBase();
			rowBase.put("id", typeName);
			rowBase.put("name", typeName);
			rowBase.put("type", "2001");
			resultList.add(rowBase);
		}
		return resultList;
	}

	/**
	 * 获取排水管网子类型 {id,name,type,fatherId}
	 */
	public List<RowBase> getChildTypeList() throws Exception {
		List<RowBase> resultList = new ArrayList<>();
		JSONObject json = JSONObject.fromObject(TYPE_JSON);
		for (Object type : json.keySet()) {
			JSONArray childTypeArray = (JSONArray) json.get(type);
			for (Object childType : childTypeArray) {
				RowBase rowBase = new RowBase();
				rowBase.put("id", childType);
				rowBase.put("name", childType);
				rowBase.put("fatherId", type);
				rowBase.put("type", "2002");
				resultList.add(rowBase);
			}
		}
		return resultList;
	}

	public List<RowBase> getProjectList() throws Exception {
		String sql = "select PROJECT_NO \"id\",PROJECT_NAME \"name\" ,DETECTION_COMPANY \"company\" from PROJECT";
		return NonNullUtil.toList(DataUtil.getSelectRows(sql, null));
	}

	public List<RowBase> getProjectItemList() throws Exception {
		String sql = "select ITEM_NO \"id\",PROJECT_NO \"fatherId\",ITEM_NAME \"name\",NVL(t2.S_DOMAINCAPTION, '未知分区') as \"unit\",DETECTION_LENGTH \"length\""
				+ " from project_item t1"
				+ " left join(select s_id,S_DOMAINCAPTION from DATA_DEFULATDOMAIN where S_DOMAINNAME = '政区' and length(s_id)='2') t2 on t2.s_id =t1.DISTRICT where 1 = 1";
		return NonNullUtil.toList(DataUtil.getSelectRows(sql, null));
	}

	public List<RowBase> getPipeList() throws Exception {
		String sql = "SELECT PIPE_NO \"id\",PIPE_SECTION_NAME \"name\",PROJECT_ITEM_NO \"fatherId\" FROM PIPE_SECTION ORDER BY PROJECT_ITEM_NO ASC";
		return NonNullUtil.toList(DataUtil.getSelectRows(sql, null));
	}

	public List<RowBase> getEndoscopicDefectList(String projectId, String projectItemId, String pipeId, String defectId) throws Exception {
		List<GlobalParameter> parms = new ArrayList<GlobalParameter>();
		String sql = "select t.LNO \"id\",t.PROJECT_ITEM_NO \"projectItemNo\",t.PIPE_NO \"pipeNo\" ,t.FLOW \"flow\""
				+ ",t.PIPE_TYPE \"pipeType\",t.PIPE_DIAMETER \"pipeDiameter\""

				+ ",NVL(t1.NAME,t.PIPE_MATERIAL) \"pipeMaterial\",t.SURVEYOR \"surveyor\""

				+ ",TO_CHAR(t.DETECTION_DATE,'hh-mm-dd hh24:mi:ss') as \"dateTime\",t.START_WELL_NO \"startWellNo\""
				+ ",t.END_WELL_NO \"endWellNo\",t.DIRECTION \"direction\",NVL(t5.S_DOMAINCAPTION,t.DEFECT_TYPE) \"defectType\""
				+ ",t.DEFECT_LEVEL \"defectLevel\",t2.S_DOMAINCAPTION \"defectLevelDes\",t.RI \"ri\""

				+ ",NVL(t3.S_DOMAINCAPTION,t.RI_LEVEL) \"riLevel\""

				+ ",t3.S_DOMAINCAPTION \"riLevelDes\",t.RI_SUGGESS \"riSuggest\",t.MI \"mi\",t.MI_LEVEL \"miLevel\""

				+ ",NVL(t4.S_DOMAINCAPTION,t.MI_LEVEL) \"miLevelDes\",t.MI_SUGGESS \"miSuggest\",t.LENGTH \"length\",t.PHOTO \"photoUrl\""

				+ ",t.VIDEO \"videoUrl\" from NKJC_DEFECT t"
				+ " LEFT JOIN PIPE_MATERIAL t1 on t1.PIPE_MATERIAL_ID = t.PIPE_MATERIAL"
				+ " LEFT JOIN (select * from DATA_DEFULATDOMAIN where S_DOMAINNAME='DEFECT_LEVEL')t2 ON t2.S_DOMAINCODE = t.DEFECT_LEVEL"
				+ " LEFT JOIN (select * from DATA_DEFULATDOMAIN  where S_DOMAINNAME='RI_LEVEL')t3 ON t3.S_DOMAINCODE = t.RI_LEVEL"
				+ " LEFT JOIN (select * from DATA_DEFULATDOMAIN  where S_DOMAINNAME='MI_LEVEL')t4 ON t4.S_DOMAINCODE = t.MI_LEVEL"
				+ " LEFT JOIN (select * from DATA_DEFULATDOMAIN  where S_DOMAINNAME='DEFECT_TYPE')t5 ON t5.S_DOMAINCODE = t.DEFECT_TYPE OR t5.S_DOMAINCAPTION = t.DEFECT_TYPE"
				+ " LEFT JOIN PROJECT_ITEM t6 ON t6.ITEM_NO = t.PROJECT_ITEM_NO"
				+ " where 1 = 1";
		if (defectId != null && !defectId.isEmpty()) {
			parms.add(new GlobalParameter("defectId", defectId));
			sql += " and t.LNO = @defectId";
		} else if (pipeId != null && !pipeId.isEmpty()) {
			parms.add(new GlobalParameter("pipeId", pipeId));
			sql += " and t.PIPE_NO = @pipeId";
		} else if (projectItemId != null && !projectItemId.isEmpty()) {
			parms.add(new GlobalParameter("projectItemId", projectItemId));
			sql += " and t.PROJECT_ITEM_NO = @projectItemId";
		} else if (projectId != null && !projectId.isEmpty()) {
			parms.add(new GlobalParameter("projectId", projectId));
			sql += " and t6.PROJECT_NO = @projectId";
		} else {
			throw new IllegalArgumentException("参数异常");
		}

		sql += " ORDER BY t.PROJECT_ITEM_NO , t.PIPE_NO";
		return NonNullUtil.toList(DataUtil.getSelectRows(sql, parms));
	}

	private RowBase getPanelPercent(String projectCode) throws Exception {
		if (mDataModel == null) {
			mDataModel = new DataModel();
		}
		List<GlobalParameter> parms = new ArrayList<GlobalParameter>();
		parms.add(new GlobalParameter("projectCode", projectCode));
		String sql = "select PROJECT_NO,STATISTICS_BOUND,WELL_NUM,SCALE,NOTE,NOTE_ALL ,DIFF_TYPE"
				+ " from ATTR_VIEW_POSITION_DIFF WHERE PROJECT_NO = @projectCode and DIFF_TYPE = 'panel'";
		List<RowBase> rows = DataUtil.getSelectRows(sql, parms);
		if (CollectionUtils.isEmpty(rows)) {
			rows = mDataModel.CalcPanelDiffFromDB(projectCode);
		}
		RowBase resultRowBase = null;
		if (!CollectionUtils.isEmpty(rows)) {
			List<RowBase> valueList = parseToPrecisionRowBase(rows);
			RowBase firstRowBase = rows.get(0);
			resultRowBase = new RowBase();
			resultRowBase.put("valueList", valueList);
			resultRowBase.put("type", "平面偏差统计");
			resultRowBase.put("noteAll", firstRowBase.getSafeString("NOTE_ALL"));
		}
		return resultRowBase;
	}

	private RowBase getHeightPercent(String projectCode) throws Exception {
		if (mDataModel == null) {
			mDataModel = new DataModel();
		}
		List<GlobalParameter> parms = new ArrayList<GlobalParameter>();
		parms.add(new GlobalParameter("projectCode", projectCode));
		String sql = "select PROJECT_NO,STATISTICS_BOUND,WELL_NUM,SCALE,NOTE,NOTE_ALL ,DIFF_TYPE"
				+ " from ATTR_VIEW_POSITION_DIFF WHERE PROJECT_NO = @projectCode and DIFF_TYPE = 'height'";
		List<RowBase> rows = DataUtil.getSelectRows(sql, parms);
		if (CollectionUtils.isEmpty(rows)) {
			rows = mDataModel.CalcHeightDiffFromDB(projectCode);
		}
		RowBase resultRowBase = null;
		if (!CollectionUtils.isEmpty(rows)) {
			List<RowBase> valueList = parseToPrecisionRowBase(rows);
			RowBase firstRowBase = rows.get(0);
			resultRowBase = new RowBase();
			resultRowBase.put("valueList", valueList);
			resultRowBase.put("type", "高程偏差统计");
			resultRowBase.put("noteAll", firstRowBase.getSafeString("NOTE_ALL"));
		}
		return resultRowBase;
	}

	// [{name,wellNum,percent}]
	private List<RowBase> parseToPrecisionRowBase(List<RowBase> rows) throws Exception {
		List<RowBase> resultList = new ArrayList<>();
		if (!CollectionUtils.isEmpty(rows)) {
			for (RowBase rowBase : rows) {
				RowBase bean = new RowBase();
				bean.put("name", rowBase.getSafeString("STATISTICS_BOUND"));
				bean.put("wellNum", rowBase.getSafeString("WELL_NUM"));
				bean.put("percent", rowBase.getSafeString("SCALE"));
				resultList.add(bean);
			}
		}
		return resultList;
	}

}
